﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.Common;
using Microsoft.Practices.EnterpriseLibrary.Data;
using VideoClassLibrary.Model;
using System.Data;

namespace VideoClassLibrary.BusinessLogicLayer
{
    public class NewsCategoryModule
    {
        /// <summary>
        /// 添加新闻类别
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public int AddNewsCategory(V_NewsCategory model)
        {
            #region
            StringBuilder strSql=new StringBuilder();
			strSql.Append("insert into V_NewsCategory(");
            strSql.Append("Title,Description,CreatedTime,updatetime,schoolid)");

			strSql.Append(" values (");
            strSql.Append("@Title,@Description,@CreatedTime,@Updatetime,@schoolid)");
			strSql.Append(";select @@IDENTITY");
			Database db = VDataBase.CreateDatabase();
			DbCommand dbCommand = db.GetSqlStringCommand(strSql.ToString());
			db.AddInParameter(dbCommand, "Title", DbType.String, model.Title);
			db.AddInParameter(dbCommand, "Description", DbType.String, model.Description);
			db.AddInParameter(dbCommand, "CreatedTime", DbType.DateTime, model.CreatedTime);
            db.AddInParameter(dbCommand, "Updatetime", DbType.DateTime, model.Updatetime);
            db.AddInParameter(dbCommand, "schoolid",DbType.Int32,model.Schoolid);
			int result;
			object obj = db.ExecuteScalar(dbCommand);
			if(!int.TryParse(obj.ToString(),out result))
			{
				return 0;
			}
			return result;
		}
		/// <summary>
		/// 更新一条数据
		/// </summary>
        public void UpdateNewsCategory(V_NewsCategory model)
		{
			StringBuilder strSql=new StringBuilder();
			strSql.Append("update V_NewsCategory set ");
			strSql.Append("Title=@Title,");
			strSql.Append("Description=@Description,");
			strSql.Append("updatetime=@updatetime");
			strSql.Append(" where ID=@ID ");
			Database db = VDataBase.CreateDatabase();
			DbCommand dbCommand = db.GetSqlStringCommand(strSql.ToString());
			db.AddInParameter(dbCommand, "ID", DbType.Int32, model.ID);
			db.AddInParameter(dbCommand, "Title", DbType.String, model.Title);
			db.AddInParameter(dbCommand, "Description", DbType.String, model.Description);
            db.AddInParameter(dbCommand, "updatetime", DbType.DateTime, model.Updatetime);
			db.ExecuteNonQuery(dbCommand);

		}

		/// <summary>
		/// 删除一条数据
		/// </summary>
		public void DeleteNewsCategory(int ID)
		{
			
			StringBuilder strSql=new StringBuilder();
			strSql.Append("delete from V_NewsCategory ");
			strSql.Append(" where ID=@ID ");
			Database db = VDataBase.CreateDatabase();
			DbCommand dbCommand = db.GetSqlStringCommand(strSql.ToString());
			db.AddInParameter(dbCommand, "ID", DbType.Int32,ID);
			db.ExecuteNonQuery(dbCommand);

		}

		/// <summary>
		/// 得到一个对象实体
		/// </summary>
		public V_NewsCategory GetModel(int ID)
		{
			
			StringBuilder strSql=new StringBuilder();
			strSql.Append("select ID,Title,Description,CreatedTime from V_NewsCategory ");
			strSql.Append(" where ID=@ID ");
			Database db = VDataBase.CreateDatabase();
			DbCommand dbCommand = db.GetSqlStringCommand(strSql.ToString());
			db.AddInParameter(dbCommand, "ID", DbType.Int32,ID);
			V_NewsCategory model=null;
			using (IDataReader dataReader = db.ExecuteReader(dbCommand))
			{
				if(dataReader.Read())
				{
					model=ReaderBind(dataReader);
				}
			}
			return model;
		}

		/// <summary>
		/// 获得数据列表
		/// </summary>
		public DataSet GetList(string strWhere)
		{
			StringBuilder strSql=new StringBuilder();
			strSql.Append("select ID,Title,Description,CreatedTime,updateTime ");
			strSql.Append(" FROM V_NewsCategory ");
			if(strWhere.Trim()!="")
			{
				strSql.Append(" where "+strWhere);
			}
			Database db = VDataBase.CreateDatabase();
			return db.ExecuteDataSet(CommandType.Text, strSql.ToString());
		}

		

		/// <summary>
		/// 获得数据列表（比DataSet效率高，推荐使用）
		/// </summary>
		public List<V_NewsCategory> GetListArray(string strWhere)
		{
			StringBuilder strSql=new StringBuilder();
			strSql.Append("select ID,Title,Description,CreatedTime ");
			strSql.Append(" FROM V_NewsCategory ");
			if(strWhere.Trim()!="")
			{
				strSql.Append(" where "+strWhere);
			}
			List<V_NewsCategory> list = new List<V_NewsCategory>();
			Database db = VDataBase.CreateDatabase();
			using (IDataReader dataReader = db.ExecuteReader(CommandType.Text, strSql.ToString()))
			{
				while (dataReader.Read())
				{
					list.Add(ReaderBind(dataReader));
				}
			}
			return list;
		}


		/// <summary>
		/// 对象实体绑定数据
		/// </summary>
		public V_NewsCategory ReaderBind(IDataReader dataReader)
		{
			V_NewsCategory model=new V_NewsCategory();
			object ojb; 
			ojb = dataReader["ID"];
			if(ojb != null && ojb != DBNull.Value)
			{
				model.ID=(int)ojb;
			}
			model.Title=dataReader["Title"].ToString();
			model.Description=dataReader["Description"].ToString();
			ojb = dataReader["CreatedTime"];
			if(ojb != null && ojb != DBNull.Value)
			{
				model.CreatedTime=(DateTime)ojb;
			}
			return model;
		}
        /// <summary>
        /// 根据新闻分类ID查询所有
        /// </summary>
        /// <param name="Nid"></param>
        /// <returns></returns>
        public V_NewsCategory QueryNewsCategoryById(int Nid)
        {
            string sql = "select ID,Title,Description ,CreatedTime,updatetime from v_newscategory where id=@nid";
            Database db = VDataBase.CreateDatabase();
            DbCommand command = db.GetSqlStringCommand(sql.ToString());
            db.AddInParameter(command, "nid", DbType.Int32, Nid);
            DataSet ds = db.ExecuteDataSet(command);
            V_NewsCategory model = null;
            if (ds != null && ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)
            {

                DataRow dr = ds.Tables[0].Rows[0];
                model = new V_NewsCategory();
                model.CreatedTime =Convert.ToDateTime(dr["CreatedTime"].ToString());
                model.ID = Convert.ToInt32(dr["ID"]);
                model.Title = dr["title"].ToString();
                model.Description = dr["Description"].ToString();
                model.Updatetime =Convert.ToDateTime( dr["Updatetime"].ToString());
                ds = null;

            }
            return model;
        }
        /// <summary>
        /// 查询新闻分类列表
        /// </summary>
        /// <param name="schoolid"></param>
        /// <returns></returns>
        public DataSet QueryNewsCategoryByschool(int schoolid)
        {

            StringBuilder strSql = new StringBuilder();
            strSql.Append(" select id ,title from V_NewsCategory");
            strSql.Append(" where schoolid=" + schoolid);
            Database db = VDataBase.CreateDatabase();
            DbCommand command = db.GetSqlStringCommand(strSql.ToString());
            db.AddInParameter(command,"schoolid",DbType.Int32,schoolid);
            return  db.ExecuteDataSet(command);
        }

        //public DataSet QueryNewsCategoryByTitleAndCreatedTime(string title, DateTime begionTime, DateTime endTime)
        //{

        //    StringBuilder strSql = new StringBuilder();
        //    strSql.Append("select ");
        //}
		#endregion  成员方法
    }
}
